﻿Option Explicit On

Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop
Imports System.Threading
Public Class frmexcel_ctnmovement
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mysql1 As MySqlConnection = main_form.mysqlconection1

    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader


    Dim mySqlCommand1 As New MySqlCommand
    Dim mySqlAdaptor1 As New MySqlDataAdapter
    Dim mySqlReader1 As MySqlDataReader
    Public Shared idvoyage As Integer
    Dim nameVessel As String

    Public Delegate Sub DelegateSub(ByVal x As Integer)
    Private Sub frmexcel_ctnmovement_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage;"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                With ListView1.Items.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("N")
                    .SubItems.Add(mySqlReader("VOYVESNAMEN"))
                    .SubItems.Add(mySqlReader("VOYDATESN"))
                    .SubItems.Add(mySqlReader("VOYDATEEN"))
                    .SubItems.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("S")
                    .SubItems.Add(mySqlReader("VOYVESNAMES"))
                    .SubItems.Add(mySqlReader("VOYDATESS"))
                    .SubItems.Add(mySqlReader("VOYDATEES"))
                End With

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub
    Public Sub searchData()
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage where VOYAGEID like '%" & TextBoxItem2.Text & "%';"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())


                With ListView1.Items.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("N")
                    .SubItems.Add(mySqlReader("VOYVESNAMEN"))
                    .SubItems.Add(mySqlReader("VOYDATESN"))
                    .SubItems.Add(mySqlReader("VOYDATEEN"))
                    .SubItems.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("S")
                    .SubItems.Add(mySqlReader("VOYVESNAMES"))
                    .SubItems.Add(mySqlReader("VOYDATESS"))
                    .SubItems.Add(mySqlReader("VOYDATEES"))
                End With
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click
        idvoyage = ListView1.SelectedItems(0).SubItems(0).Text
        nameVessel = ListView1.SelectedItems(0).SubItems(7).Text
    End Sub

    Private Sub ButtonItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem1.Click
        searchData()
    End Sub

    Private Sub TextBoxItem2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxItem2.KeyDown
        If e.KeyCode = Keys.Enter Then
            searchData()

        End If
    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Try
                Dim t = New Thread(New ThreadStart(AddressOf excelReport))
                t.Start()
                CircularProgress1.IsRunning = True
            Catch ex As Exception

            End Try

        End If



    End Sub
    Private Sub excelReport()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape


            .Range("A1").ColumnWidth = 2.75
            .Range("B1").ColumnWidth = 14.25
            .Range("C1").ColumnWidth = 12.63
            .Range("D1").ColumnWidth = 12
            .Range("E1").ColumnWidth = 14.25
            .Range("F1").ColumnWidth = 8.38
            .Range("G1").ColumnWidth = 17.63
            .Range("H1").ColumnWidth = 15.6
            .Range("I1").ColumnWidth = 9.75
            .Range("J1").ColumnWidth = 19.38
            .Range("K1").ColumnWidth = 13.5
            .Range("L1").ColumnWidth = 16.25
            .Range("M1").ColumnWidth = 10.38

            .Range("A1:L500").Font.Name = "Tahoma"
            .Range("A1:L500").Font.Size = 10

            .Rows("1:500").rowheight = 21
            With .Range("E1:E1")
                .Merge()
                .Value = "VESSEL :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F1:H1")
                .Merge()
                .Value = mySqlReader("VSSNAM")
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft

            End With
            With .Range("E2:E2")
                .Merge()
                .Value = "VOYAGE :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F2:H2")
                .Merge()
                .Value = "S" + Format(mySqlReader("VOYAGEID"), "000")
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E3:E3")
                .Merge()
                .Value = "PORT OF LOADING : "
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F3:H3")
                .Merge()
                .Value = " YUSOB INTERNATIONAL KANTANG PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E4:E4")
                .Merge()
                .Value = "SAIL DATE :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F4:H4")
                .Merge()
                .Value = Date.Now.Date.ToString("dd-mm-yy")
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from voyage join vesmain on voyage.VOYVESIDN = vesmain.VESMAINID join booking on booking.BVOYAGE = voyage.VOYAGEID where VOYAGEID ='" & frmsearch_voyage1.idvoyage & "' ;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    If mySqlReader("BFEDS") = "1" And mySqlReader("BMOTHS") = "1" Then


                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "MOTHER VESL : " + mySqlReader("BMOTH")
                            .Characters(1, 11).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("I" + count.ToString + ":J" + count.ToString)
                            .Merge()
                            .Value = "FEEDER VESL : " + mySqlReader("BFED")
                            .Characters(1, 11).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    ElseIf mySqlReader("BFEDS") = "1" And mySqlReader("BMOTHS") = "0" Then
                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "FEEDER VESL : " + mySqlReader("BFED")
                            .Characters(1, 11).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    ElseIf mySqlReader("BFEDS") = "0" And mySqlReader("BMOTHS") = "1" Then
                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "MOTHER VESL : " + mySqlReader("BMOTH")
                            .Characters(1, 11).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    End If

                    count += 1
                    With .Range("A" + count.ToString + ":C" + count.ToString)
                        .Merge()
                        .Value = "SHIPPER : " + mySqlReader("BSHIPNAME")
                        .Characters(1, 7).Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With
                    With .Range("D" + count.ToString + ":E" + count.ToString)
                        .Merge()
                        .Value = "FORWARDER : " + mySqlReader("BCONSINAME")
                        .Characters(1, 9).Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With

                    With .Range("G" + count.ToString + ":G" + count.ToString)
                        .Merge()
                        .Value = "SCN : "
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With
                    With .Range("H" + count.ToString + ":H" + count.ToString)
                        .Merge()
                        .Value = mySqlReader("BSCN")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With
                    count += 1

                    With .Range("A" + count.ToString + ":C" + count.ToString)
                        .Merge()
                        .Value = "BOOKING NO. : " + mySqlReader("BNO")
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With

                    If mySqlReader("BTRANSHIPS") = "1" And mySqlReader("BTRANSHIP2S") = "1" Then
                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "PORT TRANSHIP : " + mySqlReader("BTRANSHIP")
                            .Characters(1, 13).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("I" + count.ToString + ":J" + count.ToString)
                            .Merge()
                            .Value = "PORT TRANSHIP2 : " + mySqlReader("BTRANSHIP2")
                            .Characters(1, 14).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With

                    ElseIf mySqlReader("BTRANSHIPS") = "1" And mySqlReader("BTRANSHIP2S") = "0" Then
                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "PORT TRANSHIP : " + mySqlReader("BTRANSHIP")
                            .Characters(1, 13).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    ElseIf mySqlReader("BTRANSHIPS") = "0" And mySqlReader("BTRANSHIP2S") = "1" Then
                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "PORT TRANSHIP2 : " + mySqlReader("BTRANSHIP2")
                            .Characters(1, 14).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    End If
                    count += 1
                    With .Range("A" + count.ToString + ":C" + count.ToString)
                        .Merge()
                        .Value = "VESSEL : " + mySqlReader("VSSNAM") + "  VOYAGE : S" + Format(mySqlReader("VOYAGEID"), "000")
                        .Characters(1, 6).Font.Bold = True

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With
                    If mySqlReader("BPORTDISS") = "1" Then
                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "PORT OF DISCHARGE : " + mySqlReader("BPORTDIS")
                            .Characters(1, 19).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    End If

                    count += 1
                    With .Range("A" + count.ToString + ":E" + count.ToString)
                        .Merge()
                        .Value = "VOLUME / CONTAINER TYPE SIZE :   " + mySqlReader("BCTNNO") + "X" + mySqlReader("BCTNTYPE")
                        .Characters(1, 30).Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With
                    If mySqlReader("BDELIVERYS") = 1 Then
                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "PORT OF DELIVERY : " + mySqlReader("BDELIVERY")
                            .Characters(1, 19).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    End If

                    count += 1
                    If mySqlReader("BFINALDESTS") = "1" Then
                        With .Range("G" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = "FINAL DESTINATION : " + mySqlReader("BFINALDEST")
                            .Characters(1, 19).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    End If
                    With .Range("A" + count.ToString + ":E" + count.ToString)
                        .Merge()
                        .Value = "COMMODITY :   " + mySqlReader("BCOM")
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With
                    count += 1
                    For J = 7 To 10
                        .Range("A" + count.ToString + ":A" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("B" + count.ToString + ":B" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("C" + count.ToString + ":C" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("D" + count.ToString + ":D" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("E" + count.ToString + ":E" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("F" + count.ToString + ":F" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("G" + count.ToString + ":G" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("H" + count.ToString + ":H" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("I" + count.ToString + ":I" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("J" + count.ToString + ":J" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("K" + count.ToString + ":K" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("L" + count.ToString + ":M" + count.ToString).Borders(J).Weight = 2 ' xlThin
                    Next
                    With .Range("A" + count.ToString + ":A" + count.ToString)
                        .Merge()
                        .Value = "NO."
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("B" + count.ToString + ":B" + count.ToString)
                        .Merge()
                        .Value = "CONTAINER NO."
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("C" + count.ToString + ":C" + count.ToString)
                        .Merge()
                        .Value = "SEAL NO."
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("D" + count.ToString + ":D" + count.ToString)
                        .Merge()
                        .Value = "LINER"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("E" + count.ToString + ":E" + count.ToString)
                        .Merge()
                        .Value = "DATE IN"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("F" + count.ToString + ":F" + count.ToString)
                        .Merge()
                        .Value = "VOY."
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("G" + count.ToString + ":G" + count.ToString)
                        .Merge()
                        .Value = "GET OUT(EMPTY)"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("H" + count.ToString + ":H" + count.ToString)
                        .Merge()
                        .Value = "GET IN(LADEN)"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("I" + count.ToString + ":I" + count.ToString)
                        .Merge()
                        .Value = "DATE OUT"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("J" + count.ToString + ":J" + count.ToString)
                        .Merge()
                        .Value = "SHIPPER"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("K" + count.ToString + ":K" + count.ToString)
                        .Merge()
                        .Value = "FORWARDER"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("L" + count.ToString + ":M" + count.ToString)
                        .Merge()
                        .Value = "TRANSPORT BY"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    count += 1

                    mysql1.Close()
                    If mysql1.State = ConnectionState.Closed Then
                        mysql1.Open()
                    End If
                    mySqlCommand1.CommandText = "Select * from borrow join booking on borrow.BOOKID= booking.BOOKINGID join voyage  on booking.BVOYAGE = voyage.VOYAGEID join ctnmain on ctnmain.CTNMAINID = borrow.CTNID join vesmain on vesmain.VESMAINID = voyage.VOYVESIDN  where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' and BOOKID='" & mySqlReader("BOOKINGID") & "';"
                    ' mySqlCommand.CommandText = 
                    mySqlCommand1.Connection = mysql1
                    mySqlAdaptor1.SelectCommand = mySqlCommand1

                    Try
                        mySqlReader1 = mySqlCommand1.ExecuteReader
                        Dim number As Integer = 1
                        While (mySqlReader1.Read())

                            For J = 7 To 7
                                .Range("A" + count.ToString + ":A" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("B" + count.ToString + ":B" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("C" + count.ToString + ":C" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("D" + count.ToString + ":D" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("E" + count.ToString + ":E" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("F" + count.ToString + ":F" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("G" + count.ToString + ":G" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("H" + count.ToString + ":H" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("I" + count.ToString + ":I" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("J" + count.ToString + ":J" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("K" + count.ToString + ":K" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("L" + count.ToString + ":L" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("M" + count.ToString + ":M" + count.ToString).Borders(J).Weight = 2
                                .Range("N" + count.ToString + ":N" + count.ToString).Borders(J).Weight = 2
                            Next

                            With .Range("A" + count.ToString + ":A" + count.ToString)
                                .Merge()
                                .Value = number

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            number += 1
                            With .Range("B" + count.ToString + ":B" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("CTNSTRING")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("C" + count.ToString + ":C" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("CTNSEALID")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("D" + count.ToString + ":D" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("CTNAGENT")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("E" + count.ToString + ":E" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("VOYDATEEN")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("F" + count.ToString + ":F" + count.ToString)
                                .Merge()
                                .Value = "N" + Format(mySqlReader1("CTNVOYN"), "000")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("G" + count.ToString + ":G" + count.ToString)
                                .Merge()
                                If mySqlReader1("TIMEDATE") Is DBNull.Value Or mySqlReader1("TIMEHHMM") Is DBNull.Value Then
                                Else
                                    .Value = mySqlReader1("TIMEDATE") + "/" + mySqlReader1("TIMEHHMM")
                                End If

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("H" + count.ToString + ":H" + count.ToString)
                                .Merge()
                                If mySqlReader1("TIMEDATEIN") Is DBNull.Value Or mySqlReader1("TIMEHHMMIN") Is DBNull.Value Then
                                Else
                                    .Value = mySqlReader1("TIMEDATEIN") + "/" + mySqlReader1("TIMEHHMMIN")
                                End If

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("I" + count.ToString + ":I" + count.ToString)
                                .Merge()
                                .Value = Date.Now.Date.ToString("dd-mm-yy")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("J" + count.ToString + ":J" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("BSHIPNAME")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("K" + count.ToString + ":K" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("BSHIPNAME")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("L" + count.ToString + ":L" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("COMNAME")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("M" + count.ToString + ":M" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("CARID")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            count += 1
                        End While
                        .Range("A" + count.ToString + ":M" + count.ToString).Borders(8).Weight = 2 ' xlThin
                        number = 0
                        mysql1.Close()
                        count += 1
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    count += 1
                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()



        End With

        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\ContainerMovement S" + frmsearch_voyage1.idvoyage + Date.Now.ToString("dd-MM-yyyy") + ".xlsx")

            excelbooks.Close()
            excelapp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp)
            excelbooks = Nothing
            excelsheets = Nothing
            excelapp = Nothing
            Dim proc As System.Diagnostics.Process

            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next
            MsgBox("Report Complete")


        Catch ex As Exception

            excelbooks.Close()
            excelapp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp)
            excelbooks = Nothing
            excelsheets = Nothing
            excelapp = Nothing
            Dim proc As System.Diagnostics.Process

            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next


        End Try
        showResult(200)

    End Sub
    Private Sub showResult(ByVal Num As Integer)
        If Label10.InvokeRequired Then
            Dim dlg As New DelegateSub(AddressOf showResult)
            Me.Invoke(dlg, Num)

        Else
            CircularProgress1.IsRunning = False

        End If
    End Sub

End Class